package com.zayats.dal;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.dao.IncorrectResultSizeDataAccessException;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import com.zayats.controller.UserController;
import com.zayats.exceptions.DataAccessDbException;
import com.zayats.exceptions.FamilyNotExistsException;
import com.zayats.exceptions.LoginOrPasswordException;
import com.zayats.model.Family;
import com.zayats.model.User;
import com.zayats.rowmapper.FamiliesRowMapper;
import com.zayats.rowmapper.UsersRowMapper;

public class JDBCFamilyRepository implements FamilyRepository {

	private static final String CREATE_FAMILY_STRING = "INSERT INTO families(name, username) VALUES (:name, :username)";
	private static final String GET_FAMILIES_FOR_USER_STRING = "SELECT * FROM families WHERE username=:username";
	private static final String ASSIGN_FAMILY_FOR_USER_STRING = "INSERT INTO users2families(username, family_id) VALUES(:username, :family_id)";
	private static final String GET_FAMILY_ID_STRING = "SELECT family_id FROM families WHERE name=:name AND username=:username";
	private static final String GET_ALL_FAMILIES_FOR_USER_STRING = "SELECT families.family_id, families.name, families.username "
			+ "FROM families,users2families "
			+ "WHERE users2families.username=:username "
			+ "AND families.family_id = users2families.family_id";
	private static final String DELETE_FAMILY_STRING = "DELETE FROM families WHERE family_id=:familyId";
	private static final String GET_PARTICIPANS_STRING = "SELECT * FROM users WHERE users.username IN (SELECT users2families.username FROM users2families WHERE family_id=:familyId)";
	private static final String GET_FAMILY_STRING = "SELECT name FROM families WHERE family_id=:familyId";
	private static final String DELETE_USER_FROM_FAMILY_STRING = "DELETE FROM users2families WHERE family_id = :familyId AND username = :username";
	private static final String CHECK_FAMILY_FOR_USER_STRING = "SELECT family_id FROM families WHERE name=:name AND username=:username";

	private static final Logger logger = Logger.getLogger(UserController.class);

	private NamedParameterJdbcTemplate jdbcTemplate;

	public JDBCFamilyRepository() {
		// TODO Auto-generated constructor stub
	}

	@Autowired
	public void setDataSource(DataSource dataSource) {
		jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
	}

	public boolean createFamily(String name, String username)
			throws DataAccessDbException {
		Map<String, Object> parameters = new HashMap<String, Object>();
		parameters.put("name", name);
		parameters.put("username", username);
		int familyId = -1;
		try {
			familyId = jdbcTemplate.queryForInt(CHECK_FAMILY_FOR_USER_STRING,
					parameters);
		} catch (Exception e) {
			logger.info("Family name for user is free.");
		}
		if (familyId != -1) {
			logger.info("User have family with such name");
			return false;
		}

		logger.info("Put family to db.");
		try {
			jdbcTemplate.update(CREATE_FAMILY_STRING, parameters);
		} catch (Exception e) {
			logger.info("Insert family exception.");
			throw new DataAccessDbException();
		}

		familyId = jdbcTemplate.queryForInt(GET_FAMILY_ID_STRING, parameters);

		assignFamilyToUser(familyId, username);

		return true;
	}

	public boolean deleteFamily(int familyId) throws DataAccessDbException {

		Map<String, Object> parameters = new HashMap<String, Object>();
		parameters.put("familyId", familyId);

		logger.info("Put family to db.");
		try {
			jdbcTemplate.update(DELETE_FAMILY_STRING, parameters);
		} catch (Exception e) {
			logger.info("Delete family exception.");
			throw new DataAccessDbException();
		}

		return true;
	}

	public List<Family> getFamiliesForUser(String username) {
		Map<String, Object> parameters = new HashMap<String, Object>();

		parameters.put("username", username);
		List<Family> families = null;
		logger.info("Get families for user:" + username + ".");
		try {
			families = jdbcTemplate.query(GET_FAMILIES_FOR_USER_STRING,
					parameters, new FamiliesRowMapper());
		} catch (EmptyResultDataAccessException e) {
			logger.info("User:" + username + " has no families.");
			return null;
		}
		return families;

	}

	public boolean assignFamilyToUser(int familyId, String username) {
		Map<String, Object> parameters = new HashMap<String, Object>();
		parameters.put("username", username);
		parameters.put("family_id", familyId);
		logger.info("Assign family to user.");
		try {
			jdbcTemplate.update(ASSIGN_FAMILY_FOR_USER_STRING, parameters);
		} catch (Exception e) {
			logger.info("Can't assign family to user.");
			return false;
		}

		return true;
	}

	public List<User> getParticipans(int familyId)
			throws EmptyResultDataAccessException, FamilyNotExistsException {
		Map<String, Object> parameters = new HashMap<String, Object>();
		parameters.put("familyId", familyId);

		try {
			jdbcTemplate.queryForObject(GET_FAMILY_STRING, parameters,
					String.class);
		} catch (EmptyResultDataAccessException e) {
			throw new FamilyNotExistsException();
		}

		List<User> users = null;
		logger.info("Get participant for family:" + familyId + ".");
		users = jdbcTemplate.query(GET_PARTICIPANS_STRING, parameters,
				new UsersRowMapper());
		return users;
	}

	public List<Family> getAllFamiliesForUser(String username) {
		Map<String, Object> parameters = new HashMap<String, Object>();
		parameters.put("username", username);

		List<Family> families = null;
		logger.info("Get all families for user:" + username + ".");
		try {
			families = jdbcTemplate.query(GET_ALL_FAMILIES_FOR_USER_STRING,
					parameters, new FamiliesRowMapper());
		} catch (EmptyResultDataAccessException e) {
			logger.info("User:" + username + " has no families.");
			return null;
		}
		return families;
	}

	public boolean deleteUserFromFamily(String username, int familyId) {
		Map<String, Object> parameters = new HashMap<String, Object>();
		parameters.put("familyId", familyId);
		parameters.put("username", username);

		jdbcTemplate.update(DELETE_USER_FROM_FAMILY_STRING, parameters);

		return true;
	}
}
